﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using NCRGEA.AAL;
using System.Data.SqlClient;
using System.IO;

namespace NCRGEA.Admin
{
    public partial class frmImportErrorList : Form
    {
        public frmImportErrorList()
        {
            InitializeComponent();
            FormatDataTable();
        }

        #region Global Variables

        #region Constants

        const int _nameField = 35;
        const int _SSNField = 9;
        const int _amountField = 8;
        const int _descriptionField = 48;

        #endregion

        DataTable _memberListing = new DataTable();

        DataTable _importingList = new DataTable();

        string _finalQuery = string.Empty;

        #endregion

        #region Data Manipulation

        private void FormatDataTable()
        {
            _memberListing.Columns.Add("SSN");
            _memberListing.Columns.Add("Name");
            _memberListing.Columns.Add("Amount");
            _memberListing.Columns.Add("Description");
            
        }

        /// <summary>
        /// Obtain the pieces of the string for splitting
        /// </summary>
        /// <param name="master"></param>
        private void ObtainStringData(string master)
        {
            Logger.Log("Splitting file line: " + master);

            int aLength = _SSNField;
            int cLength = _SSNField + _nameField;
            int sLength = _SSNField + _nameField + _amountField;
            int zLength = _SSNField + _nameField + _amountField + _descriptionField;


            string ssn = master.Substring(0, _SSNField);
            string name = master.Substring(aLength, _nameField).Trim();
            string amount = master.Substring(cLength, _amountField).Trim();
            string description = master.Substring(sLength, _descriptionField).Trim();


            _memberListing.Rows.Add(ssn, name, amount, description);
        }

        /*
        /// <summary>
        /// Splits the name into the needed pieces, first name, last name and middle name.
        /// </summary>
        /// <param name="name"></param>
        private void SplitName(string name)
        {
            Logger.Log("Splitting the first name.");

            name = name.Trim();

            string[] split = name.Split(',');

            /* Expected results
             * string a = RUDA, COREY C
             * string[] split = a.split(',')
             * 
             * firstName = split[0];
             * 
             * string split2 = split[1].split(' ');
             * 
             * string lastName = split2[0];
             * string middleName = split2[1];
             */

            /*string firstName = split[0];
            string[] split2 = split[1].Split(' ');

            string lastName = split2[0];
            string middleName = split2[1];
        }*/

        private void ChooseFile()
        {
            DialogResult result = odDialog1.ShowDialog();
            odDialog1.Filter = "Text Files|*.txt";
            string line = string.Empty;

            try
            {
                switch(result)
                {
                    case DialogResult.Cancel:
                        return;
                }

                System.IO.StreamReader sr = new System.IO.StreamReader(odDialog1.FileName);
                
                while ((line = sr.ReadLine()) != null)
                {
                    ObtainStringData(line);
                }

                sr.Close();
            }
            catch(FileNotFoundException e)
            {
                Logger.Log("User did not select a file. " + e.Message);
            }

            Logger.Log("File selection completed.");

            gcErrorGrid.DataSource = null;
            gcErrorGrid.DataSource = _memberListing;

            /*
            if (_memberListing.Rows.Count > 0 && _memberListing != null)
                btnImport.Enabled = true;
             * */
        }

        #endregion

        private void btnChooseFile_Click(object sender, EventArgs e)
        {
            ChooseFile();
            btnExport.Enabled = true;
        }

        private void btnAll_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewRow d in gcErrorGrid.Rows)
                d.Cells[0].Value = 1;
        }

        private void btnNone_Click(object sender, EventArgs e)
        {
            foreach (DataGridViewRow d in gcErrorGrid.Rows)
                d.Cells[0].Value = 0;

        }


        /// <summary>
        /// Determines if there is a duplicate member inside of the database
        /// </summary>
        /// <param name="fname"></param>
        /// <param name="lname"></param>
        /// <param name="mname"></param>
        /// <param name="address"></param>
        /// <param name="state"></param>
        /// <param name="zipcode"></param>
        /// <param name="zipfour"></param>
        /// <returns>Return true if the member is to be updated, otherwise insert a new member</returns>
        private bool DetermieDuplicate(string fname, string lname, string mname, string address, string state, string zipcode, string zipfour, string city)
        {
            string query = @"SELECT
                                *
                             FROM
                                memInfo
                            WHERE
                                (FirstName = @FirstName AND LastName = @LastName) OR (Address1 = @Address AND State = @State AND (City = @City OR Zip = @Zipcode OR ZipFour = @Zipfour))";

            SqlParameter[] param = new SqlParameter[7];

            param[0] = new SqlParameter("@FirstName", fname);
            param[1] = new SqlParameter("@LastName", lname);
            param[2] = new SqlParameter("@Address", address);
            param[3]= new SqlParameter("@State", state);
            param[4] = new SqlParameter("@Zipcode", zipcode);
            param[5] = new SqlParameter("@Zipfour", zipfour);
            param[6] = new SqlParameter("@City", city);

            DataTable exist = DAL.ExecuteQuery(query, param);

            if (exist.Rows.Count > 0 && exist != null)
            {
                string message = "A member already exisits with similar information. \n" + fname + " " + mname + " " + lname + " " + address + " " + zipfour;

                if (zipfour != string.Empty)
                    message = message + "-" + zipfour;

                message = message + "\nDo you wish to update?";

                switch (MessageBox.Show(message, "Duplicate Member", MessageBoxButtons.YesNo))
                {
                    case DialogResult.Yes:
                        //UpdateCurrentMember(fname, lname, mname, address, state, zipcode, zipfour,city);
                        return true;
                    case DialogResult.No:
                        //SaveNewMember(fname, lname, mname, address, state, zipcode, zipfour, city);
                        return false;
                }
            }
            else
            {
                SaveNewMember(fname, lname, mname, address, state, zipcode, zipfour,city);
                return true;
            }

            return false;
        }

        /// <summary>
        /// Creates the insert string to insert the mebmer into the database
        /// </summary>
        /// <param name="fname"></param>
        /// <param name="lname"></param>
        /// <param name="mname"></param>
        /// <param name="address"></param>
        /// <param name="state"></param>
        /// <param name="zipcode"></param>
        /// <param name="zipfour"></param>
        private void SaveNewMember(string fname, string lname, string mname, string address, string state, string zipcode, string zipfour, string city)
        {
            string query = @"INSERT INTO
                                memInfo (FirstName, MiddleName, LastName, Address1, State, Zip, Zipfour, TypeID, City)
                            VALUES
                                (@FirstName, @MiddleName, @LastName,@Address,@State, @Zipcode,@Zipfour,2,@City)";

            SqlParameter[] param = new SqlParameter[8];

            param[0] = new SqlParameter("@FirstName", fname);
            param[1] = new SqlParameter("@LastName", lname);
            param[2] = new SqlParameter("@Address", address);
            param[3]= new SqlParameter("@State", state);
            param[4] = new SqlParameter("@Zipcode", zipcode);
            param[5] = new SqlParameter("@Zipfour", zipfour);
            param[6] = new SqlParameter("@MiddleName", mname);
            param[7] = new SqlParameter("@City", city);

            DAL.ExecuteNonQuery(query, param);
        }

        private void UpdateCurrentMember(string fname, string lname, string mname, string address, string state, string zipcode, string zipfour, string city)
        {
            string query = @"UPDATE
                                memInfo 
                            SET
                                FirstName = @FirstName, MiddleName = @MiddleName, LastName = @LastName, Address1 = @Address, State = @State, 
                                Zip = @Zipcode, Zipfour = Zipfour
                            WHERE
                                FirstName = @FirstName AND LastName= @LastName";

            SqlParameter[] param = new SqlParameter[8];

            param[0] = new SqlParameter("@FirstName", fname);
            param[1] = new SqlParameter("@LastName", lname);
            param[2] = new SqlParameter("@Address", address);
            param[3] = new SqlParameter("@State", state);
            param[4] = new SqlParameter("@Zipcode", zipcode);
            param[5] = new SqlParameter("@Zipfour", zipfour);
            param[6] = new SqlParameter("@MiddleName", mname);
            param[7] = new SqlParameter("@City", city);

            DAL.ExecuteNonQuery(query, param);
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            if (gcErrorGrid.RowCount > 0)
            {
                ObtainEligableDentalExports();
            }
            else
            {
                MessageBox.Show("Please select a valid file to export.", "No File Selected");
                btnExport.Enabled = false;
            }
        }

        private void ObtainEligableDentalExports()
        {
            string query = @"SELECT
                                *
                             FROM
                                memInfo
                            WHERE
                                DentalFlag = 'A' OR DentalFlag = 'U' AND TypeID = 0";

            DataTable eligableDental = new DataTable();

            query = @"SELECT
                        *
                        FROM
                        memPlans mp
                            INNER JOIN
                                memPrograms mrp
                            ON
                                mp.ProgramID = mrp.ProgramID";

            if (gcErrorGrid.Rows.Count > 0)
            {
                string queryPart = "SSN <> ";

                for (int i = 0; i < gcErrorGrid.Rows.Count; i++)
                {
                    string partialLine = string.Empty;

                    partialLine += " AND ";

                    DataTable ssnTable = (DataTable)gcErrorGrid.DataSource;

                    

                    //DetermieDuplicate(fname,lname,mname,address,state,zipcode,zipfour,city);

                    partialLine += queryPart;
                    partialLine += ssnTable.Rows[i]["SSN"];
                    query += partialLine;
                }
            }

            eligableDental = DAL.ExecuteQuery(query);

            //ExportDental(eligableDental);
        }

        /// <summary>
        /// Formats the string of the data table passed in the correct format. Export type lets us know if it was a dental
        /// or a vision export. 0 = Dental 1 = Vision exporting
        /// </summary>
        /// <param name="table"></param>
        /// <param name="exportType"></param>
        private void FormatExporting(DataTable table)
        {
            List<string> finalFormattedList = new List<string>();
            
            string tempFormat = string.Empty;

            foreach (DataRow row in table.Rows)
            {
                    
            }
        }

        /// <summary>
        /// Write the data to a textbox.
        /// TODO: Write to an FTP server
        /// </summary>
        /// <returns>Returns true if the file was written to sucessfully. False if it was unable to write to the file</returns>
        private bool ExportDental(DataTable dataList, string amount)
        {
            string beginingFileName = "MET";

            DateTime date = DateTime.Today;

            int determineMonth = date.Month;
            string monthAbbrv = string.Empty;

            switch (determineMonth)
            {
                case 1:
                    monthAbbrv = "jan";
                    break;
                case 2:
                    monthAbbrv = "feb";
                    break;
                case 3:
                    monthAbbrv = "mar";
                    break;
                case 4:
                    monthAbbrv = "apr";
                    break;
                case 5:
                    monthAbbrv = "may";
                    break;
                case 6:
                    monthAbbrv = "jun";
                    break;
                case 7:
                    monthAbbrv = "jul";
                    break;
                case 8:
                    monthAbbrv = "aug";
                    break;
                case 9:
                    monthAbbrv = "sep";
                    break;
                case 10:
                    monthAbbrv = "oct";
                    break;
                case 11:
                    monthAbbrv = "nov";
                    break;
                case 12:
                    monthAbbrv = "dec";
                    break;
            }

            beginingFileName += monthAbbrv + " " + date.Year + ".txt";

            const string location = "C:\\Users\\corey\\Desktop\\Development\\Test Exporting\\";
            Logger.Log("Begining Exporting Script.");

            string formattedEntry = string.Empty;
            
            if (!(File.Exists(location + beginingFileName)))
            {
                using (System.IO.StreamWriter file = new System.IO.StreamWriter(location + beginingFileName))
                {
                    foreach (DataRow a in dataList.Rows)
                    {
                        string fname = a["FirstName"].ToString();
                        string lname = a["LastName"].ToString();
                        string mname = a["MiddleName"].ToString();
                        string address = a["Address1"].ToString();
                        string state = a["State"].ToString();
                        string zipcode = a["Zip"].ToString();
                        string zipfour = a["ZipFour"].ToString();
                        string city = a["City"].ToString();

                        DateTime bDate = (DateTime)a["DOB"];
                        DateTime rDate = (DateTime)a["RetirementDate"];

                        string retirementDate = String.Format("{0:MM/dd/yyyy}", rDate);
                        string birthday = String.Format("{0:MM/dd/yyyy}", bDate);
                        string dateString = String.Format("{0:MM/dd/yyyy}", date);


                        formattedEntry += fname.PadRight(25);
                        formattedEntry += lname.PadRight(26);
                        //formattedEntry += amount;
                        formattedEntry += dateString;
                        formattedEntry += address.PadRight(40);
                        formattedEntry += city.PadRight(27);
                        formattedEntry += state;
                        formattedEntry += zipcode;
                        formattedEntry += birthday;
                        formattedEntry += retirementDate;

                        file.WriteLine(formattedEntry);
                    }
                }
            }
            else
            {
                using (System.IO.StreamWriter file = new System.IO.StreamWriter(location + beginingFileName, true))
                {
                    foreach (DataRow a in dataList.Rows)
                    {
                        file.WriteLine(a);
                    }
                }
            }

            return true;
        }
    }
}
